﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Obj;


namespace DAL
{

    /// <summary>
    /// Summary description for SQLModelo
    /// </summary>
    public class SQLModelo : dataAccess
    {
        public SQLModelo()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        /// <summary>
        /// Atualiza todos os modelos de um determinada marca para null. Metodo deve ser utilizado antes de excluir uma Marca. 
        /// </summary>
        /// <param name="marcaID"></param>
        /// <returns></returns>
        public int UpdateByMarca(int marcaID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_UpdateByMarca", con);
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = marcaID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return ret;
            }
        }

        /// <summary>
        /// Apaga um registro da tabela modelo. 
        /// </summary>
        /// <param name="modeloID"></param>
        /// <returns></returns>
        public bool Delete(int modeloID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_Delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Value = modeloID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Atualiza um registro da tabela Modelo
        /// </summary>
        /// <param name="modelo"></param>
        /// <returns></returns>
        public bool Update(objModelo modelo)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_Update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = modelo.Descricao;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = modelo.MarcaID;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Value = modelo.ModeloID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);

            }
        }

        /// <summary>
        /// insere um novo registro na tabela Modelo
        /// </summary>
        /// <param name="modelo"></param>
        /// <returns></returns>
        public int Insert(objModelo modelo)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_Insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = modelo.Descricao;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = modelo.MarcaID;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Direction = ParameterDirection.Output;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@modeloID"].Value;
            }
        }

        /// <summary>
        /// Seleciona o Modelo pelo codigo recebido. 
        /// </summary>
        /// <param name="marcaID"></param>
        /// <returns></returns>
        public List<objModelo> SelectByMarca(int marcaID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_selectByMarca", con);
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = marcaID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// retorna todos os modelos cadastrados na tabela Modelo por ordem de descricao. 
        /// </summary>
        /// <returns></returns>
        public List<objModelo> SelectAll()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Retorna os modelos a  partir da descricao recebida
        /// </summary>
        /// <param name="descricao"></param>
        /// <returns></returns>
        public List<objModelo> SelectAll(string descricao)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_selectByDescricao", con);
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = descricao;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Retorna o Modelo a partir do Codigo recebido
        /// </summary>
        /// <param name="modeloID">Codigo do Modelo</param>
        /// <returns></returns>
        public objModelo SelectByID(int modeloID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("modelo_SelectByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Value = modeloID;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }



        private objModelo obj(IDataReader reader)
        {
            objModelo modelo = new objModelo(
                                                (int)reader["modeloID"],
                                                 reader["descricao"].ToString(),
                                                (int)reader["marcaID"],
                                                reader["descricaoMarca"].ToString()
                                                );
            return modelo;
        }

        private List<objModelo> objLista(IDataReader reader)
        {
            List<objModelo> modelos = new List<objModelo>();
            while (reader.Read())
                modelos.Add(obj(reader));
            return modelos;

        }
    }
}